
//
//  SQLiteManager.swift
//  SQL_FMDB
//
//  Created by Mr.Run on 2017/9/12.
//  Copyright © 2017年 Mr.Run. All rights reserved.
//

import Foundation
import FMDB

class SQLiteManager {
    
    static let shared = SQLiteManager()
    let queue: FMDatabaseQueue?
    private init() {
        
        let dbName = "status.db"
        var path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
        path = (path as NSString).appendingPathComponent(dbName)
        print(path)
        queue = FMDatabaseQueue(path: path)
        createTable()
    }
    
}

extension SQLiteManager {
    
    func loadStatus(userId: String, since_id: Int64=0, max_id: Int64=0) -> [[String: Any]] {
        
        var sql = "SELECT statusId, userId, status FROM T_Status\n"
        sql.append("WHERE userId = \(userId)\n")
        
        if since_id > 0 {
            sql.append("AND statusId > \(since_id)\n")
        }else if max_id > 0{
            sql.append("AND statusId < \(max_id)\n")
        }
        
        sql.append("ORDER BY statusId DESC LIMIT 20;")
        
        let arr = execRecordSet(sql: sql)
//        print(arr)
        var result = [[String: Any]]()
        
        for dict in arr {
            
            guard let jsonData = dict["status"] as? Data,
                let json = try? JSONSerialization.jsonObject(with: jsonData, options: []) as? [String: Any]
                else {
                    continue
            }
//            print(json ?? [:])
            result.append(json ?? [:])
        }
        
        return result
        //40006599010
    }
    
    // 新增或者修改数据
    func updateStatus(userId: String, array: [[String: Any]]) {
        // 1. 准备语句
        /**
         status -> 完整微博字典的 json 二进制数据
        */
        let sql = "INSERT OR REPLACE INTO T_Status (statusId, userId, status) VALUES (?, ?, ?);"
        
        // 2. 执行语句
        queue?.inTransaction { (db, rollback) in
            
            for dict in array {
                
                guard let statusId = dict["idstr"],
                    let jsonData = try? JSONSerialization.data(withJSONObject: dict, options: [])
                    else{
                        continue
                }
                
                if db.executeUpdate(sql, withArgumentsIn: [statusId, userId, jsonData]) == false {
                    // FIXME: 回滚
                    
                    rollback.pointee = true
                    
                    break
                }
                
            }
            
        }
        
        
    }
    
}

extension SQLiteManager {
    
    func execRecordSet(sql: String) -> [[String: Any]] {
        
        var result = [[String: Any]]()
        
        queue?.inDatabase({ (db) in
            guard let rs = db.executeQuery(sql, withArgumentsIn: []) else{
                return
            }
            
            while rs.next() {
                let colCount = rs.columnCount
                
                for col in 0..<colCount {
                    
                    guard let name = rs.columnName(for: col),
                        let value = rs.object(forColumnIndex: col)
                        else{
                            continue
                    }
//                    print(name, value)
                    result.append([name: value])
                }
                
            }
        })
        
        
        return result
    }
    
    func createTable() {
        guard let path = Bundle.main.path(forResource: "status.sql", ofType: nil),
            let sql = try? String(contentsOfFile: path)
            else {
                return
        }
        
        queue?.inDatabase { (db) in
            if db.executeStatements(sql) == true {
                print("创表成功")
            }else{
                print("失败")
            }
        }
        print("over")
    }
    
}














